from django.shortcuts import render
from web import models
import collections
import datetime
import time
from django.db.models import Count
from django.http import JsonResponse


def dashboard(request, project_id):
    """概览"""
    status_dict = collections.OrderedDict()  # 有序字典
    for key, text in models.Issues.status_choices:
        status_dict[key] = {'text': text, 'count': 0}

    # 根据status分组，并对每一组，进行计算一下他们的数量并赋值ct
    issues_data = models.Issues.objects.filter(project_id=project_id).values('status').annotate(ct=Count('id'))
    # print(issues_data)
    for item in issues_data:
        status_dict[item['status']]['count'] = item['ct']

        # 项目参与者

    user_list = models.ProjectUser.objects.filter(project_id=project_id).values('user_id', 'user__username')
    # print(user_list)
    # print(status_dict)

    # 最近的10个问题
    top_ten = models.Issues.objects.filter(project_id=project_id, assign__isnull=True).order_by('-id')[0:10]
    context = {'status_dict': status_dict,
               'user_list': user_list,
               'top_ten_object': top_ten}
    return render(request, 'web/dashboard.html', context)


def issues_chart(request, project_id):
    """在概览页面生成highcharts所需数据"""

    # 一般 select id,email from table
    # select id,name,strftime("%Y-%m-%d",create_datetime) as ctime from table
    # 没咋么看懂 记得看232这个视频

    # select xxxx, 1 as ctime from xxxx
    # select xxxx,1 as ctime from xxxx
    # select id,name,email from table;
    # select id,name, strftime("%Y-%m-%d",create_datetime) as ctime from table;
    # strftime('%%Y-%%m-%%d',web_issues.create_datetime) 这个是sql字符串格式化函数
    # "DATE_FORMAT(web_transaction.create_datetime,'%%Y-%%m-%%d')"  这个是mysql字符串格式化函数
    # 最近30天每天创建的问题数量  & 进行日期分组  web_issues 是Issues在数据库真正的表名 web_issues
    today = datetime.datetime.now().date()  # 只获取年月日
    date_dict = collections.OrderedDict()  # 创建有序字典
    for i in range(0, 30):
        date = today - datetime.timedelta(days=i)
        date_dict[date.strftime('%Y-%m-%d')] = [time.mktime(date.timetuple()) * 1000, 0]  # 当前时间转化为时间挫
        """
        {
          2021-02-10:[162940439050,0],
          2021-02-9:[162940439050,0]
        }
        """
    # print(date_dict)
    result = models.Issues.objects.filter(project_id=project_id,
                                          create_datetime__gte=today - datetime.timedelta(days=30)).extra(select={
        'ctime': "DATE_FORMAT(issues.create_datetime,'%%Y-%%m-%%d')"}).values('ctime').annotate(ct=Count('id'))
    # 相当于额外生成一个ctime字段
    # print(result)

    for item in result:
        date_dict[item['ctime']][1] = item['ct']
    print(date_dict)
    return JsonResponse({'status': True, 'data': list(date_dict.values())})
